﻿use persons
go
------------------10---------------------------------------------------------------
/*Find all grandfaathers who has son */
SELECT  
[Person].[FirstName], 
[Person].[LastName]  
FROM [dbo].[Person]
WHERE [Person].[ID] IN(SELECT DISTINCT  
[Person].[fk_fatherId]
FROM [dbo].[Person] 
WHERE  [Person].[ID] IN (SELECT DISTINCT 
[Person].[fk_fatherId]
FROM [dbo].[Person] 
WHERE [Person].[fk_fatherId] IS NOT NULL))

SELECT  
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person]
WHERE [Person].[ID] IN(SELECT DISTINCT  
[Person].[fk_fatherId]
FROM [dbo].[Person] 
WHERE  [Person].[ID] IN (SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person], [dbo].[Person] AS [FATHER] 
WHERE [Person].[ID]=[FATHER].[fk_fatherId] AND [Person].[fk_fatherId] IS NOT NULL))


SELECT  
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person]
WHERE [Person].[ID] IN(SELECT DISTINCT  
[Person].[fk_fatherId]
FROM [dbo].[Person] 
WHERE  [Person].[ID] IN (SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [FATHER] ON [Person].[ID]=[FATHER].[fk_fatherId] 
WHERE [Person].[fk_fatherId] IS NOT NULL))

